/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package cn.com.jsepc.rep.dsdd;

import cn.com.jsepc.rep.comm.ConstUtil;
import cn.com.jsepc.rep.report.ImportXls;
import cn.com.jsepc.rep.vo.DemensionVO;
import cn.com.jsepc.rep.vo.RepDataVO;
import cn.com.jsepc.rep.vo.RepDemensionVO;
import cn.com.jsepc.rep.vo.SetValueVO;
import com.jarp.db.DAO;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.Enumeration;
import java.util.Hashtable;
import java.util.Iterator;
import java.util.Set;
import java.util.Vector;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.servlet.ServletException;

/**
 *
 * @author 赵远
 * @time 2013-6-8
 */
public class DsddAutoSetValue {

    private static DAO dao = new DAO();
    private static int PAGECOUNT = 1000;

    /**
     * 开始定时调度，该方法抽取数据库里的所有数据
     */
    public void run() {
    	Date date=new Date();
    	SimpleDateFormat sdf=new SimpleDateFormat("yyyyMMdd");
    	int currentDate=Integer.valueOf(sdf.format(date));
    	System.out.println(currentDate+"的定时调度开始进行");
        String sql = "SELECT * FROM TB_SETVALUE t WHERE CAST(t.STARTTIME AS INT)<="+currentDate+" AND CAST(t.ENDTIME AS INT)>="+currentDate;  //读取数据库里的所有配置完成的定时调度任务
        System.out.println(sql);
        try {
            Vector v = dao.getDataSet(ConstUtil.DBNAME, sql);
            if (v != null) {
                Iterator<Hashtable> iterator = v.iterator();
                while (iterator.hasNext()) {
                    Hashtable hash = iterator.next();
                    int startTime=Integer.valueOf((String)hash.get("STARTTIME"));
                    int endTime=Integer.valueOf((String)hash.get("ENDTIME"));
                    String addtime=(String)hash.get("ADDTIME");
                    if(addtime==null){
                    	addtime="";
                    }
                    SetValueVO setValueVO = this.getValue((String) hash.get("ID"));
                    String repid=setValueVO.getRepid();
                    String thisTime=this.getThisTime(repid);
                    if(addtime.equals(thisTime)){  //说明该指标的值已经录入
                    	System.out.println(setValueVO.getId()+"这个任务已经执行过了");
                       continue;	
                    }
                    Hashtable setValueHash = this.getValueHash((String) hash.get("ID"));
                    if (setValueVO != null) {  //可以找到该条数据，无法找到记录的错误一般不会存在
                        StringBuffer col = new StringBuffer();  //获取维度的列名
                        StringBuffer demtab = new StringBuffer();//维度表
                        StringBuffer demVal = new StringBuffer();//维度值
                        StringBuffer colWan = new StringBuffer();
                        StringBuffer demtabWan = new StringBuffer();
                        StringBuffer demValWan = new StringBuffer();
                        RepDataVO repVO = new ImportXls().getRepDemension(setValueVO.getRepid());   //获取该指标的实体类
                        ArrayList<RepDemensionVO> demlist = repVO.getRepDemLst();
                        int colNum = demlist.size();   //该指标关联了几个维度
                        boolean iswcz = false;  //判断用户是否选择了完成值
                        for (int i = 0; i < colNum; i++) {  //开始遍历维度
                            for (RepDemensionVO repDem : demlist) {
                                if (setValueHash.get("DIM" + (i + 1)).equals(repDem.getFactdimcol())) {
                                    col.append(repDem.getDimtable()).append(".").append(repDem.getIdcol()).append(" as ").append(repDem.getFactdimcol()).append(",");
                                    demtab.append(repDem.getDimtable()).append(",");
                                    if (repDem.getDemtype().equals("2")) {  //该维度是时间维度
                                        demVal.append(" and ").append(repDem.getDimtable()).append(".").append(repDem.getIdcol()).append(" in (").append(this.jugeTimeDem(setValueVO.getRepid(), (String) setValueHash.get("DIMVALUE" + (i + 1)))).append(") ");
                                    } else {
                                        demVal.append(" and ").append(repDem.getDimtable()).append(".").append(repDem.getIdcol()).append(" in (").append(setValueHash.get("DIMVALUE" + (i + 1))).append(") ");
                                    }
                                }
                                //完成值
                                if (setValueHash.get("DIMWAN" + (i + 1)).equals(repDem.getFactdimcol())) {
                                    if (setValueHash.get("DIMVALUEWAN" + (i + 1)).equals("")) { //说明用户未勾选该完成值维度
                                        colWan.append("99999 as ").append(repDem.getFactdimcol()).append(",");
                                    } else {
                                        if (repDem.getDemtype().equals("2")) {
                                            demValWan.append(" and ").append(repDem.getDimtable()).append(".").append(repDem.getIdcol()).append(" in (").append(this.jugeTimeDem(setValueVO.getRepid(), (String) setValueHash.get("DIMVALUEWAN" + (i + 1)))).append(") ");
                                        } else {
                                            demValWan.append(" and ").append(repDem.getDimtable()).append(".").append(repDem.getIdcol()).append(" in (").append(setValueHash.get("DIMVALUEWAN" + (i + 1))).append(") ");
                                        }
                                        colWan.append(repDem.getDimtable()).append(".").append(repDem.getIdcol()).append(" as ").append(repDem.getFactdimcol()).append(",");
                                        demtabWan.append(repDem.getDimtable()).append(",");
                                        iswcz = true;
                                    }
                                }
                            }
                        }
                        StringBuffer sqls = new StringBuffer();  //普通的维度筛选
                        StringBuffer sqlsWan = new StringBuffer(); //完成值维度筛选
                        sqls.append("select ").append(this.plusdot(col)).append(" from ").append(this.plusdot(demtab)).append(" where 1=1 ").append(demVal);
                        if (iswcz == true) {
                            sqlsWan.append("select ").append(this.plusdot(colWan)).append(" from ").append(this.plusdot(demtabWan)).append(" where 1=1 ").append(demValWan);
                            
                            this.insertSQL(sqlsWan.toString(), setValueVO);
                        }
                        System.out.println(sqls.toString());
                        this.insertSQL(sqls.toString(), setValueVO);
                    } else {
                        System.out.println("该条记录为空！");
                        continue;
                    }
                    String updateSql="UPDATE TB_SETVALUE SET ADDTIME='"+thisTime+"' WHERE ID="+setValueVO.getId();
                    dao.execCommand(ConstUtil.DBNAME, updateSql);
                }
                
            } else {
                System.out.println("结果集为空，可能是没有任务或者数据库连接失败！");
            }
        } catch (ServletException ex) {
            Logger.getLogger(DsddAutoSetValue.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    /**
     * 将生成的数据插入到事实表中中
     */
    public void insertSQL(String sql, SetValueVO setValueVO) {
        try {
            int columCount = dao.getDataSetCount(ConstUtil.DBNAME, sql);  //获取结果集的行数
            int page = columCount % PAGECOUNT == 0 ? columCount / PAGECOUNT : columCount / PAGECOUNT + 1;      //获取结果集需要分多少次
            ArrayList<String> finalSQL = new ArrayList<String>();   //最后插入事实表的SQL语句
            for (int i = 1; i <= page; i++) {
                Vector v1 = dao.getDataSet(ConstUtil.DBNAME, sql, i, PAGECOUNT);
                if (v1 != null) {
                    Iterator<Hashtable> iterator1 = v1.iterator();
                    String sql1 = "";   //插入事实表的单条SQL语句
                    while (iterator1.hasNext()) {
                        StringBuffer factCol = new StringBuffer();  //事实表列
                        StringBuffer factVal = new StringBuffer(); //事实表值
                        Hashtable hash1 = iterator1.next();
                        Enumeration enumer = hash1.keys();
                        while (enumer.hasMoreElements()) {
                            String colNam = (String) enumer.nextElement();
                            String Colvalue = (String) hash1.get(colNam);
                            factCol.append(colNam).append(",");
                            factVal.append(Colvalue).append(",");
                        }
                        factCol.append("REPVAL,SUMVAL,PREVAL,TPREVAL,TSUMVAL,TREPVAL");
                        factVal.append(setValueVO.getRepvalue()).append(",").append(setValueVO.getSumvalue());
                        factVal.append(",").append(setValueVO.getPreval()).append(",").append(setValueVO.getTpreval()).append(",").append(setValueVO.getTsumval()).append(",").append(setValueVO.getTrepval());
                        sql1 = "insert into " + setValueVO.getRefact() + "(" + factCol.toString() + ") values (" + factVal.toString() + ")";
                        finalSQL.add(sql1);
                    }
                    System.out.println("findSQL:\n"+finalSQL);
                    dao.execCommand(ConstUtil.DBNAME, finalSQL);
                }
            }
        } catch (ServletException ex) {
            Logger.getLogger(DsddAutoSetValue.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    /**
     * 判断时间维度，过滤未到当前时间的值
     *
     * @return
     */
    public String jugeTimeDem(String repid, String str) {
        int thisTime = Integer.valueOf(this.getThisTime(repid));
//        str = str.trim();
//        String[] demVal = str.split(",");
//        String newstr = "";
//        for (String str1 : demVal) {
//            int val = Integer.valueOf(str1);
//            if (val <= thisTime) {
//                newstr += val + ",";
//            }
//        }
 //       return this.plusdot(newstr);
        return String.valueOf(thisTime);
    }

    /**
     * 获取setValue的实体类
     *
     * @param id
     * @return
     */
    public SetValueVO getValue(String id) {
        SetValueVO setValueVO = new SetValueVO();
        String sql = "SELECT * FROM TB_SETVALUE t WHERE t.ID=" + id;
        try {
            Vector v = dao.getDataSet(ConstUtil.DBNAME, sql);
            if (v != null) {
                Hashtable hash = (Hashtable) v.get(0);
                setValueVO.setId((String) hash.get("ID"));
                setValueVO.setRepid((String) hash.get("REPID"));
                setValueVO.setRefact((String) hash.get("REFACT"));
                setValueVO.setRoleid((String) hash.get("ROLEID"));
                setValueVO.setTime((String) hash.get("TIME"));
                setValueVO.setDim1((String) hash.get("DIM1"));
                setValueVO.setDimvalue1((String) hash.get("DIMVALUE1"));
                setValueVO.setDim2((String) hash.get("DIM2"));
                setValueVO.setDimvalue2((String) hash.get("DIMVALUE2"));
                setValueVO.setDim3((String) hash.get("DIM3"));
                setValueVO.setDimvalue3((String) hash.get("DIMVALUE3"));
                setValueVO.setDim4((String) hash.get("DIM4"));
                setValueVO.setDimvalue4((String) hash.get("DIMVALUE4"));
                setValueVO.setDim5((String) hash.get("DIM5"));
                setValueVO.setDimvalue5((String) hash.get("DIMVALUE5"));
                setValueVO.setDim6((String) hash.get("DIM6"));
                setValueVO.setDimvalue6((String) hash.get("DIMVALUE6"));
                setValueVO.setDim7((String) hash.get("DIM7"));
                setValueVO.setDimvalue7((String) hash.get("DIMVALUE7"));
                setValueVO.setDimwan1((String) hash.get("DIMWAN1"));
                setValueVO.setDimvaluewan1((String) hash.get("DIMVALUEWAN1"));
                setValueVO.setDimwan2((String) hash.get("DIMWAN2"));
                setValueVO.setDimvaluewan2((String) hash.get("DIMVALUEWAN2"));
                setValueVO.setDimwan3((String) hash.get("DIMWAN3"));
                setValueVO.setDimvaluewan3((String) hash.get("DIMVALUEWAN3"));
                setValueVO.setDimwan4((String) hash.get("DIMWAN4"));
                setValueVO.setDimvaluewan4((String) hash.get("DIMVALUEWAN4"));
                setValueVO.setDimwan5((String) hash.get("DIMWAN5"));
                setValueVO.setDimvaluewan5((String) hash.get("DIMVALUEWAN5"));
                setValueVO.setDimwan6((String) hash.get("DIMWAN6"));
                setValueVO.setDimvaluewan6((String) hash.get("DIMVALUEWAN6"));
                setValueVO.setDimwan7((String) hash.get("DIMWAN7"));
                setValueVO.setDimvaluewan7((String) hash.get("DIMVALUEWAN7"));
                setValueVO.setRepvalue((String) hash.get("REPVALUE"));
                setValueVO.setSumvalue((String) hash.get("SUMVALUE"));
                setValueVO.setTrepval((String) hash.get("TREPVAL"));
                setValueVO.setTsumval((String) hash.get("TSUMVAL"));
                setValueVO.setPreval((String) hash.get("PREVAL"));
                setValueVO.setTpreval((String) hash.get("TPREVAL"));
                setValueVO.setAddtime((String) hash.get("ADDTIME"));
                setValueVO.setStarttime((String) hash.get("STARTTIME"));
                setValueVO.setEndtime((String) hash.get("ENDTIME"));

            } else {   //找不到结果
                setValueVO = null;
            }
        } catch (ServletException ex) {
            Logger.getLogger(DsddAutoSetValue.class.getName()).log(Level.SEVERE, null, ex);
        }
        return setValueVO;
    }

    /**
     * 获取setvalue的hash结果集
     *
     * @param id
     * @return
     */
    public Hashtable getValueHash(String id) {
        Hashtable hash = new Hashtable();
        String sql = "SELECT * FROM TB_SETVALUE t WHERE t.ID=" + id;
        try {
            Vector v = dao.getDataSet(ConstUtil.DBNAME, sql);
            if (v != null) {
                hash = (Hashtable) v.get(0);
            }
        } catch (ServletException ex) {
            Logger.getLogger(DsddAutoSetValue.class.getName()).log(Level.SEVERE, null, ex);
        }
        return hash;
    }

    /**
     * 去掉字符串最后的逗号
     *
     * @param str
     * @return
     */
    public String plusdot(StringBuffer str) {
        String res = "";
        res = str.toString().trim();
        if (res.charAt(res.length() - 1) == ',') {
            res = res.substring(0, res.length() - 1);
        }
        return res;
    }

    public String plusdot(String str) {
        String res = "";
        res = str.trim();
        if (res.charAt(res.length() - 1) == ',') {
            res = res.substring(0, res.length() - 1);
        }
        return res;
    }

    /**
     * 获取当期时间
     *2013年9月12日修改：
     *新需求要求自动生成上期值，时间需要往前推
     * @param repid
     * @return
     */
    public String getThisTime(String repid) {
        String thisTime = "";
        String timeCol = new ImportXls().getTimeCol(repid); // 获取时间维度
        if (timeCol == null || timeCol.equals("")) {
            System.out.println("该指标没有关联时间维度！");
            return "";
        }
        if (timeCol.equals("YEARID")) { // 时间维度是年
            Date date = new Date();
            Calendar calendar = Calendar.getInstance();
            calendar.setTime(date);
            calendar.add(Calendar.YEAR, -1);
            date = calendar.getTime();
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy");
            thisTime = sdf.format(date);
        } else if (timeCol.equals("YEARMONTHID")) { // 时间维度是年月
            Date date = new Date();
            Calendar calendar = Calendar.getInstance();
            calendar.setTime(date);
            calendar.add(Calendar.MONTH, -1);
            date = calendar.getTime();
            SimpleDateFormat sdf = new SimpleDateFormat("yyyyMM");
            thisTime = sdf.format(date);
        } else if (timeCol.equals("YEARMONTHDAYID")) { // 时间维度是年月日
            Date date = new Date();
            Calendar calendar = Calendar.getInstance();
            calendar.setTime(date);
            calendar.add(Calendar.DAY_OF_YEAR, -1);
            date = calendar.getTime();
            SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd");
            thisTime = sdf.format(date);
        } else if (timeCol.equals("YEARQUERTERID")) { // 时间维度是季度
            Date date = new Date();
            Calendar calendar = Calendar.getInstance();
            calendar.setTime(date);
            calendar.add(Calendar.MONTH, -3);
            date = calendar.getTime();
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy");
            String year = sdf.format(date);
            sdf = new SimpleDateFormat("MM");
            String month = sdf.format(date);
            int mon = Integer.valueOf(month);
            int qua = mon % 3 == 0 ? mon / 3 : mon / 3 + 1;
            thisTime = year + "0" + qua;
        } else if (timeCol.equals("HALFYEARID")) { // 时间维度是半年
            Date date = new Date();
            Calendar calendar = Calendar.getInstance();
            calendar.setTime(date);
            calendar.add(Calendar.MONTH, -6);
            date = calendar.getTime();
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy");
            String year = sdf.format(date);
            sdf = new SimpleDateFormat("MM");
            String month = sdf.format(date);
            int mon = Integer.valueOf(month);
            int qua = mon % 6 == 0 ? mon / 6 : mon / 6 + 1;
            thisTime = year + "0" + qua;
        } else if (timeCol.equals("YEARMONTHWEEKID")) { // 时间维度是周
            Date date = new Date();
            Calendar calendar = Calendar.getInstance();
            calendar.setTime(date);
            calendar.add(Calendar.WEEK_OF_YEAR, -1);
            date = calendar.getTime();
            SimpleDateFormat sdf = new SimpleDateFormat("yyyyww");
            thisTime = sdf.format(date);
        }
        return thisTime;
    }
}
